package com.jiuwu.note.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.jiuwu.note.po.ShopDetailsPO;
import com.jiuwu.note.po.ShopEventPO;

@Repository
public class ShopEventDao {
	
	@Autowired
	private JdbcTemplate jdbcTemplate ;
	
	
	
	public int addEvent(ShopEventPO po) {

		// String sql = "INSERT INTO t_hot_goods (goods_name,maning_price,add_time)
		// VALUES ( '爱他美【3段】奶粉' , 218 , now() );";
		String sql = "INSERT INTO t_shop_events (shop_event_name,exchange_rate_real,exchange_rate_sell,time) VALUES ( ? , ? , ? , ? );";
		return jdbcTemplate.update(sql,
				new Object[] { po.getTitle(), po.getExchangeRateReal(), po.getExchangeRateSell(), new Date() });
	}
	
	
	
	public List<ShopEventPO> getAllEvent(){
		String sql = "SELECT T._id,T.shop_event_name,T.exchange_rate_real,T.exchange_rate_sell,T.time FROM t_shop_events AS T;";
		final List<ShopEventPO> list = new ArrayList<>();
		RowMapper<ShopEventPO> rowMapper = new RowMapper<ShopEventPO>() {
			
			@Override
			public ShopEventPO mapRow(ResultSet rs, int rowNum) throws SQLException {
				ShopEventPO po = new ShopEventPO();
				po.setId(rs.getInt("_id"));
				po.setTitle(rs.getString("shop_event_name"));
				po.setExchangeRateReal(rs.getFloat("exchange_rate_real"));
				po.setExchangeRateSell(rs.getFloat("exchange_rate_sell"));
				po.setTime(rs.getDate("time"));
				list.add(po);
				return po;
			}
		};
		
		jdbcTemplate.query(sql, new Object[] {}, rowMapper);
		return list;
	}
	
	
	
	
	public ShopEventPO getShopEventById(int id) {
		String sql = "SELECT T._id,T.shop_event_name,T.exchange_rate_real,T.exchange_rate_sell,T.time FROM t_shop_events AS T WHERE T._id = ?;";
		return  jdbcTemplate.queryForObject(sql, new Object[] {id}, new RowMapper<ShopEventPO>(){

			@Override
			public ShopEventPO mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				ShopEventPO po = new ShopEventPO();
				po.setId(rs.getInt("_id"));
				po.setTitle(rs.getString("shop_event_name"));
				po.setExchangeRateReal(rs.getFloat("exchange_rate_real"));
				po.setExchangeRateSell(rs.getFloat("exchange_rate_sell"));
				po.setTime(rs.getDate("time"));
				return po;
			}
			
		});
		
	}
	
//	CREATE TABLE `t_shop_details` (
//			  `_id` int(11) NOT NULL AUTO_INCREMENT,
//			  `goods_name` varchar(50) NOT NULL,
//			  `goods_price` decimal(10,2) NOT NULL,
//			  `shop_event_id` int(11) NOT NULL,
//			  `shoper_id` int(11) NOT NULL,
//			  `goods_num` int(11) NOT NULL DEFAULT '1',
//			  `exchange_rate` decimal(4,3) NOT NULL,
//			  `addtime` datetime DEFAULT NULL,
	public List<ShopDetailsPO> getShopDetailsByEventId(int eventId){
		String sql =  "SELECT TA.shoper_name,T._id,T.goods_name,T.goods_price,T.goods_num,T.exchange_rate,T.shoper_id FROM t_shopers AS TA INNER JOIN t_shop_details AS T ON (TA._id = T.shoper_id) WHERE T.shop_event_id = ?;";
		return queryForList(sql,new Object[] {eventId});
		
	}
	
	public List<ShopDetailsPO> getShopDetailsByEventIdAndShoper(int eventId,Integer shoperId){
		String sql =  "SELECT T._id,T.goods_name,T.goods_price,T.goods_num,T.exchange_rate FROM t_shop_details AS T WHERE T.shop_event_id = ? AND T.shoper_id = ?;";
		return queryForList(sql,new Object[] {eventId,shoperId});
		
	}
	
	
	private List<ShopDetailsPO> queryForList(String sql,Object[] params){
		final List<ShopDetailsPO> list = new ArrayList<>();
		RowMapper<ShopDetailsPO> rowMapper = new RowMapper<ShopDetailsPO>() {
			
			@Override
			public ShopDetailsPO mapRow(ResultSet rs, int rowNum) throws SQLException {
				ShopDetailsPO po = new ShopDetailsPO();
				po.setId(rs.getInt("_id"));
				po.setGoodsName(rs.getString("goods_name"));
				po.setGoodsPrice(rs.getFloat("goods_price"));
				po.setGoodsNum(rs.getInt("goods_num"));
				po.setExchangeRate(rs.getFloat("exchange_rate"));
				po.setShoperName(rs.getString("shoper_name"));
				list.add(po);
				return po;
			}
		};
		jdbcTemplate.query(sql, params, rowMapper);
		return list;
	}
	
	
	public int addDetails(ShopDetailsPO details) {
		String sql = "INSERT INTO t_shop_details (goods_name,goods_price,shop_event_id,shoper_id,goods_num,exchange_rate,addtime) VALUES ( ? , ? , ? , ? , ? , ? , ?);";
		return jdbcTemplate.update(sql, new Object[] {details.getGoodsName(),details.getGoodsPrice(),details.getShopEventId(),
				details.getShoperId(),details.getGoodsNum(),details.getExchangeRate(),new Date()});
	}
	
	

}
